package com.hjc.springbootmovies.mapper;

import com.hjc.springbootmovies.entity.ModelSale;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: HJC
 * Date: 2019/12/6
 * Time: 10:09
 * To change this template use File | Settings | File Templates.
 */
@Mapper
public interface MappingSale {

    @Select("SELECT * FROM sale WHERE mid=#{mid}")
    ModelSale queryById(int id);

    @Select("SELECT s.mid,s.xid, f.fname, h.tname,b.begintime,b.endtime,b.xmoney,s.mzw " +
            "FROM sale s , broad b,hall h,fiml f " +
            "WHERE s.xid = b.xid and b.tid = h.tid and b.fid = f.fid")
    @Results({
            @Result( property = "mid" , column = "mid"),
            @Result( property = "xid" , column = "xid"),
            @Result( property = "mzw" , column = "mzw"),
            @Result( property = "modelBroad" , column = "xid" , one = @One(select = "com.hjc.springbootmovies.mapper.MappingBorad.queryById" , fetchType = FetchType.EAGER )),
    })
    List<ModelSale> querySale();

//    @Select("select s.mzw , b.xmoney " +
//            "from sale s " +
//            "left join broad b on s.xid = b.xid " +
//            "where s.xid = #{xid}")
//    @Results({
//            @Result( property = "mzw" , column = "mzw"),
//            @Result( property = "xid" , column = "xid"),
//            @Result( property = "modelBroad" , column = "xid" , one = @One(select = "com.hjc.springbootmovies.mapper.MappingBorad.queryById" , fetchType = FetchType.EAGER )),
//    })
    @Select("SELECT s.mid,s.xid, f.fname, h.tname,b.begintime,b.endtime,b.xmoney,s.mzw " +
            "FROM sale s , broad b,hall h,fiml f " +
            "WHERE s.xid = b.xid and b.tid = h.tid and b.fid = f.fid and s.xid = #{xid}")
    @Results({
            @Result( property = "mid" , column = "mid"),
            @Result( property = "xid" , column = "xid"),
            @Result( property = "mzw" , column = "mzw"),
            @Result( property = "modelBroad" , column = "xid" , one = @One(select = "com.hjc.springbootmovies.mapper.MappingBorad.queryById" , fetchType = FetchType.EAGER )),
    })
    List<ModelSale> queryById2(Integer xid);

    @Select("select mzw from sale where xid = (select xid from broad where tid = #{tid} and fid = #{fid} limit 1 )")
    List<ModelSale> queryMzw(@Param("tid")int tid , @Param("fid")int fid );

    @Delete("DELETE FROM sale WHERE mid=#{mid} ")
    int deleteById(int mid);

    @Insert("insert into sale (xid,mzw) values ( (select xid from broad where fid = #{fid} and tid = #{tid} limit 1) , #{mzw} )")
    @Options( useGeneratedKeys = true , keyColumn = "mid")
    int addSale(@Param("fid")int fid , @Param("tid")int tid , @Param("mzw")int mzw);
}
